Snowflake同スキーマにおいて、ロールごとに公開範囲を変えたVIEWを設定って可能ですか?
みなさんこんにちは、クルトンです。
今回はSnowflake同スキーマ内において、VIEWに対してロールごとに公開範囲を変えられるのか確かめてみました。
想定しているのは同じ対象のテーブルにおいて、複数ロールで表示するけれども表示するデータ(行)が異なる場合で、元テーブルは表示しない場合です。
つまりは、閲覧用のロールに、必要最低限のもの以外は見せないようにできないか確かめたく思いました。
行アクセスポリシーというものもありますが、データが整っている場合はVIEWを設定してあげれば各閲覧者用にうまく表示できると思います。そこでVIEWの公開を特定ロールのみに限定できるか調査してみました。
前提
すでにデータベースやウェアハウスなどがある状態からスタートしています。また、権限周りで困らないために強い権限を持つACCOUNTADMINロールで実行しています。オブジェクト作成するようなSQLを実行した場合は、最後のスキーマ削除の部分は必ず行なうようにお願いいたします。
やってみた
準備段階
まずは必要な各種オブジェクトを作っていきます。
-- ロール作成
CREATE ROLE TEST_A;
CREATE ROLE TEST_B;
-- スキーマ作成
CREATE SCHEMA PUBLIC_DATA;
CREATE SCHEMA ORIGINAL_DATA;
-- データの準備
CREATE TABLE ORIGINAL_DATA.ORIGINAL_DATA_TABLE(role_name varchar, val int);
INSERT INTO ORIGINAL_DATA.ORIGINAL_DATA_TABLE VALUES ('TEST_A', 1), ('TEST_B', 2);
データが無事に入っているか確認するには、以下のSQLで確認します。
-- データが入っているか確認
SELECT * FROM ORIGINAL_DATA.ORIGINAL_DATA_TABLE;
各ロール用のVIEW作成
まずは1つ目のVIEWを作成します。
-- 公開用スキーマにてVIEWを作成
USE SCHEMA PUBLIC_DATA;
-- TEST_A用のVIEWを作成
CREATE VIEW TEST_A_VIEW AS SELECT * FROM ORIGINAL_DATA.ORIGINAL_DATA_TABLE WHERE ROLE_NAME='TEST_A';
次のSQLを実行すると、2行のうち、1行めのみ見えるのが確認できます。
SELECT * FROM TEST_A_VIEW;
次に2つ目のVIEWを作成します。
-- TEST_B用のVIEWを作成
CREATE VIEW TEST_B_VIEW AS SELECT * FROM ORIGINAL_DATA.ORIGINAL_DATA_TABLE WHERE ROLE_NAME='TEST_B';
以下のSQLを実行すると2行めのみ見えるのが確認できました。
SELECT * FROM TEST_B_VIEW;
VIEWの公開範囲を権限調整
ここまで用意した各ロール用のVIEWを、それぞれのロールからのみSELECTできるように権限調整します。
-- 権限調整
---- DB
GRANT USAGE ON DATABASE <データベース名> TO ROLE TEST_A;
GRANT USAGE ON DATABASE <データベース名> TO ROLE TEST_B;
---- ウェアハウス
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE TEST_A;
GRANT USAGE ON WAREHOUSE <ウェアハウス名> TO ROLE TEST_B;
---- スキーマ
GRANT USAGE ON SCHEMA PUBLIC_DATA TO ROLE TEST_A;
GRANT USAGE ON SCHEMA PUBLIC_DATA TO ROLE TEST_B;
---- 特定のVIEW
GRANT SELECT ON VIEW PUBLIC_DATA.TEST_A_VIEW TO ROLE TEST_A;
GRANT SELECT ON VIEW PUBLIC_DATA.TEST_B_VIEW TO ROLE TEST_B;
---- ユーザーに付与
GRANT ROLE TEST_A TO USER <VIEWを作成したユーザー名>;
GRANT ROLE TEST_B TO USER <VIEWを作成したユーザー名>;
重要なのが、以下のVIEWに対する権限調整するSQLです。
GRANT SELECT ON VIEW PUBLIC_DATA.TEST_A_VIEW TO ROLE TEST_A;
GRANT SELECT ON VIEW PUBLIC_DATA.TEST_B_VIEW TO ROLE TEST_B;
VIEW名を細かく指定する事で、「どのスキーマ」「どのVIEW」がSELECTできるのかを設定できます。
動作確認
権限調整が終わりましたら、実際に各VIEWがロールごとに表示できるかどうかを確認します。
以下で実行しているUSE ROLE <ロール名>
で使用するロールを変更すると、左サイドの表示が以下のようにPUBLIC_DATAスキーマ以外は新規作成したものは表示されないはずです。
まずはTEST_Aロールで、TEST_A_VIEWのみが表示できるのかを確認しましょう。
-- 動作確認
USE ROLE TEST_A;
SELECT * FROM TEST_B_VIEW; -- アクセスできない
権限がないためエラーが出力されるのを確認できます。
次にロールに割り振ったVIEWが問題なく表示できるか確認します。
SELECT * FROM TEST_A_VIEW; -- アクセスして表示可能
問題なく表示できましたね。
あとは、念のためにTEST_Bロールにおいても同様に確認してみましょう。
USE ROLE TEST_B;
SELECT * FROM TEST_A_VIEW; -- アクセスできない
SELECT * FROM TEST_B_VIEW; -- アクセスして表示可能
TEST_Aロールと同様に、片方のVIEWのみ見える事を確認できました!
後片付け
最後に不要なオブジェクトを削除してしまいましょう。スキーマに結びついているオブジェクトしかないため、スキーマ削除で全て削除完了です!
USE ROLE ACCOUNTADMIN;
DROP SCHEMA ORIGINAL_DATA;
DROP SCHEMA PUBLIC_DATA;
終わりに
今回は、VIEWの公開範囲をロールベースで権限調整できるか確かめてみました。実はVIEWだけでなく、テーブルにおいても同様の設定が可能です。
本日はここまで。
それでは、また!